package netective.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Date;

import netective.api.gatherer.Captor;
import netective.api.analyzer.ByteConverter;
import netective.api.analyzer.PacketDirection;
import netective.api.analyzer.PacketType;
import netective.gui.charts.ChartGrain;

import org.h2.tools.Server;

import jpcap.NetworkInterfaceAddress;
import jpcap.packet.ARPPacket;
import jpcap.packet.ICMPPacket;
import jpcap.packet.IPPacket;
import jpcap.packet.TCPPacket;
import jpcap.packet.UDPPacket;

public class DatabaseHelper 
{
	
	private Connection conn = null;
	private Server server=null;
	private String user="user";
	private String pass="pass";
	private Captor captor;
	private static int activeDeviceFK = -1;
	private Timestamp lastAnalyze;
	
	public void setlastAnalyze(Timestamp lastAnalyze) {
		this.lastAnalyze = lastAnalyze;
	}

	public DatabaseHelper() 
	{
		openDatabase();
	}
	
	public DatabaseHelper(Captor captor)
	{
		this.captor=captor;
		lastAnalyze=new Timestamp(0);
		openDatabase();
	}
		
	public void openDatabase() 
	{
        try {
        	if (server==null||server.getStatus().equals("Not started"))
        	{
        		server = Server.createTcpServer(new String[] { "-tcpAllowOthers" }).start();
        	}
			Class.forName("org.h2.Driver");
	        conn= DriverManager.getConnection("jdbc:h2:tcp://localhost/packetdb", user,pass);
	        createTables();
		} 
        catch (ClassNotFoundException e) 
		{
        	System.out.println(e.getMessage());
		}
        catch (SQLException e) 
        {
        	System.out.println(e.getMessage());	        
		}
	}
	
	private void createTables()
	{
		String query = "CREATE TABLE IF NOT EXISTS Devices " +
						"(id int NOT NULL AUTO_INCREMENT, " +
						"mac_address varchar(50), " +
						"description varchar(100)," +
						"CONSTRAINT uk_mac_address UNIQUE (mac_address))";

		executeQuery(query);

		query = "CREATE TABLE IF NOT EXISTS TCPPacket " +
				"(id int NOT NULL AUTO_INCREMENT, " +
				"timestamp timestamp, " +
				"version int, " +
				"length int, " +
				"ident int, " +					
				"source_ip varchar(40), " +
				"dest_ip varchar(40), " +
				"source_port int, " +
				"dest_port int," +
				"sequence varchar(20), " +
				"ack_num varchar(20), "+
				"urg boolean, " +
				"ack boolean, " +
				"psh boolean, " +
				"rst boolean, " +
				"syn boolean, " +
				"fin boolean, " +
				"flags int, " +
				"device int, " +
				"direction boolean," +
				"FOREIGN KEY (device) REFERENCES Devices(id))";
		
		executeQuery(query);
		
		query = "CREATE TABLE IF NOT EXISTS UDPPacket " +
				"(id int NOT NULL AUTO_INCREMENT, " +
				"timestamp timestamp, " +
				"version int, " +
				"length int, " +
				"ident int, " +					
				"source_ip varchar(40), " +
				"dest_ip varchar(40), " +
				"source_port int, " +
				"dest_port int, " +
				"device int, " +
				"direction boolean," +
				"FOREIGN KEY (device) REFERENCES Devices(id))";
		
		executeQuery(query);
		
		query = "CREATE TABLE IF NOT EXISTS ICMPPacket " +
				"(id int NOT NULL AUTO_INCREMENT, " +
				"timestamp timestamp, " +
				"version int, " +
				"length int, " +
				"ident int, " +					
				"source_ip varchar(40), " +
				"dest_ip varchar(40), " +
				"type int, " +
				"code int, "+
				"device int, " +
				"direction boolean," +
				"FOREIGN KEY (device) REFERENCES Devices(id))";
		
		executeQuery(query);
		
		query = "CREATE TABLE IF NOT EXISTS ARPPacket " +
				"(id int NOT NULL AUTO_INCREMENT, " +
				"timestamp timestamp, " +
				"length int, " +
				"htype int, "+
				"hlen int, "+
				"oper int, "+
				"ptype int, "+
				"plen int, "+
				"sha varchar(50), "+
				"spa varchar(50), "+
				"tha varchar(50), "+
				"tpa varchar(50), "+
				"device int, " +
				"direction boolean," +
				"FOREIGN KEY (device) REFERENCES Devices(id))";
		
		executeQuery(query);
		
		query = "CREATE TABLE IF NOT EXISTS IPPacket " +
				"(id int NOT NULL AUTO_INCREMENT, " +
				"timestamp timestamp, " +
				"length int, " +
				"source_ip varchar(40), " +
				"dest_ip varchar(40), " +
				"protocol int," +
				"device int, " +
				"direction boolean," +
				"FOREIGN KEY (device) REFERENCES Devices(id))";

		executeQuery(query);
		
		query = "CREATE TABLE IF NOT EXISTS Logs " +
				"(id int NOT NULL AUTO_INCREMENT, " +
				"timestamp timestamp, " +
				"log_type varchar(20), " +
				"message varchar(4096))";

		executeQuery(query);
		
		executeQuery(createAgregation("classesOfPackets",10));
		executeQuery(createAgregation("dirrectionOfPackets",3));
		executeQuery(createAgregation("downloadUpload",3));
		executeQuery(createAgregation("downloadUploadAvg",3));
		executeQuery(createAgregation("outPort",5));
		executeQuery(createAgregation("inPort",5));
		executeQuery(createAgregation("otherPort",5));
		executeQuery(createAgregation("otherAdresses",32));
		
	}
	
	public String createAgregation(String table, int classFieldSize)
	{
		String query = "CREATE TABLE IF NOT EXISTS "+table+
				"(year smallint, " +
				"month tinyint, " +
				"day tinyint, " +
				"hour tinyint, " +
				"minute tinyint, " +
				"class varchar("+classFieldSize+"), " +
				"protocol varchar(10), "+
				"packetsCount int)";
		return query;
	}
	
	public static void setActiveDeviceFK(int deviceId)
	{
		activeDeviceFK = deviceId;
	}
	
	public void addNewDevice(String macAddress, String description)
	{
		String query = "INSERT INTO Devices " +
						"(mac_address, " +
						"description) " +
						"VALUES (\'" + 
						 macAddress + "\', \'" +
						 description + "\')";

		System.out.println(query);
		executeQuery(query);
	}
	
	public int getDeviceFK(String macAddress)
	{
		String query = "SELECT id " +
						"from Devices " +
						"where REPLACE(mac_address, ':', '') = \'" + macAddress.replaceAll(":", "") + "\'";
		
		try 
		{
			ResultSet result = executeQueryAndGetResult(query);
			if(result.first())
			{
				return result.getInt(1);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		return -1;
	}

	public void savePacket(TCPPacket packet) 
	{
		int flags = ((packet.urg?1:0)<<5) + ((packet.ack?1:0)<<4) + ((packet.psh?1:0)<<3)+((packet.rst?1:0)<<2)+((packet.syn?1:0)<<1)+(packet.fin?1:0);
		
		String ip="";
		for (NetworkInterfaceAddress a : captor.getDevices()[captor.getActivedevice()].addresses) 
		{
			ip += a.address + " ";
		}
	//	System.out.println(ip);
		
		
		String query = "INSERT INTO TCPPacket " +
					  	"(timestamp, " +
					  	"version, " +
					  	"length, " +
					  	"ident, " +
					  	"source_ip, " +
					  	"dest_ip, " +
					  	"source_port, " +
					  	"dest_port, " +
					  	"sequence, " +
					  	"ack_num, " +
					  	"urg, " +
					  	"ack, " +
					  	"psh, " +
					  	"rst, " +
					  	"syn, " +
					  	"fin, " +
					  	"flags, " +
					  	"device, " +
					  	"direction) "+
						"VALUES " +
						"(\'"+new Timestamp(packet.sec*1000L)+"\', "+
						packet.version+", "+
						packet.length+", "+
						packet.ident+", \'"+
						packet.src_ip.toString().replace("/", "")+"\', \'"+
						packet.dst_ip.toString().replace("/", "")+"\', "+
						packet.src_port+", "+
						packet.dst_port+", "+
						packet.sequence+", "+
						packet.ack_num+", "+
						packet.urg+", "+
						packet.ack+", "+
						packet.psh+", "+
						packet.rst+", "+
						packet.syn+", "+
						packet.fin+","+
						flags+", "+
						activeDeviceFK+", " +
						(ip.indexOf(packet.src_ip.toString()+" ")>=0?1:0)+")";
		
		executeQuery(query);
	}

	public void savePacket(UDPPacket packet) 
	{
		String ip="";
		for (NetworkInterfaceAddress a : captor.getDevices()[captor.getActivedevice()].addresses) 
		{
			ip += a.address + " ";
			
		}

		String query = "INSERT INTO UDPPacket " +
				 		"(timestamp, " +
				 		"version, " +
				 		"length, " +
				 		"ident, " +
				 		"source_ip, " +
				 		"dest_ip, " +
				 		"source_port, " +
				 		"dest_port, " +
				 		"device, " +
				 		"direction) "+
				 		"VALUES "+
				 		"(\'"+new Timestamp(packet.sec*1000L)+"\', "+
				 		packet.version+", "+
				 		packet.length+", "+
				 		packet.ident+", \'"+
				 		packet.src_ip.toString().replace("/", "")+"\', \'"+
				 		packet.dst_ip.toString().replace("/", "")+"\', "+
				 		packet.src_port+", "+
				 		packet.dst_port+", "+
				 		activeDeviceFK+", " +
				 		(ip.indexOf(packet.src_ip.toString()+" ")>=0?1:0)+")";
		
		executeQuery(query);
	}

	public void savePacket(ICMPPacket packet) 
	{
		String ip="";
		for (NetworkInterfaceAddress a : captor.getDevices()[captor.getActivedevice()].addresses) 
		{
			ip += a.address + " ";
		}		
		
		String query = "INSERT INTO ICMPPacket " +
				 		"(timestamp, " +
				 		"version, " +
				 		"length, " +
				 		"ident, " +
				 		"source_ip, " +
				 		"dest_ip, " +
				 		"type, " +
				 		"code, " +
				 		"device, " +
				 		"direction) "+
				 		"VALUES "+
				 		"(\'"+new Timestamp(packet.sec*1000L)+"\', "+
				 		packet.version+", "+
				 		packet.length+", "+
				 		packet.ident+", \'"+
				 		packet.src_ip.toString().replace("/", "")+"\', \'"+
				 		packet.dst_ip.toString().replace("/", "")+"\', "+
				 		packet.type+", "+
				 		packet.code+", "+
				 		activeDeviceFK+", " +
				 		(ip.indexOf(packet.src_ip.toString()+" ")>=0?1:0)+")";
		
		executeQuery(query);		
	}

	public void savePacket(ARPPacket packet) 
	{
		String mac="";
		for (byte b : captor.getDevices()[captor.getActivedevice()].mac_address) 
		{
			mac += Integer.toHexString(b & 0xff) + ":";
		}
		
		String query = "INSERT INTO ARPPacket " +
				 		"(timestamp, " +
				 		"length, " +
				 		"htype, " +
				 		"hlen, " +
				 		"oper, " +
				 		"ptype, " +
				 		"plen, " +
				 		"sha, " +
				 		"spa, " +
				 		"tha, " +
				 		"tpa, " +
				 		"device, " +
				 		"direction) "+
				 		"VALUES " +
				 		"(\'"+new Timestamp(packet.sec*1000L)+"\', "+
				 		packet.len+", "+
				 		packet.hardtype+", "+
				 		packet.hlen+", "+
						packet.operation+", "+
						packet.prototype+", "+
						packet.plen+", \'"+
						ByteConverter.fromByteToHexString(packet.sender_hardaddr)+"\', \'"+
						ByteConverter.fromByteToString(packet.sender_protoaddr)+"\', \'"+
						ByteConverter.fromByteToHexString(packet.target_hardaddr)+"\', \'"+
						ByteConverter.fromByteToString(packet.target_protoaddr)+"\' , "+
						activeDeviceFK+", " +
						(mac.equals(ByteConverter.fromByteToHexString(packet.sender_hardaddr)+":")?1:0)+")";
		
		executeQuery(query);
	}

	public void savePacket(IPPacket packet) 
	{
		String ip="";
		for (NetworkInterfaceAddress a : captor.getDevices()[captor.getActivedevice()].addresses) 
		{
			ip += a.address + " ";
		}

		
		String query = "INSERT INTO IPPacket " +
				 		"(timestamp, " +
				 		"length, " +
				 		"source_ip, " +
				 		"dest_ip, " +
				 		"protocol, " +
				 		"device, " +
				 		"direction) "+
				 		"VALUES " +
				 		"(\'"+new Timestamp(packet.sec*1000L)+"\', "+
				 		packet.len+", \'"+
				 		packet.src_ip.toString().replace("/", "")+"\', \'"+
				 		packet.dst_ip.toString().replace("/", "")+"\', "+
				 		packet.protocol+", "+
				 		activeDeviceFK+", " + 
				 		(ip.indexOf(packet.src_ip.toString()+" ")>=0?1:0)+")";
		
		executeQuery(query);
	}
	
	public int getPacketsCount(PacketType packetType, Date start, Date finish) 
	{
		String packetTypeStr = packetType.toString();
		String query = "SELECT COUNT(1) FROM " + packetTypeStr;
		if (start != null) 
		{
			query += " WHERE TIMESTAMP >= '" + start.toString() +"'";
		}
		if (finish != null) 
		{
			if (start != null) 
			{
				query += " AND ";
			} 
			else 
			{
				query += " WHERE ";
			}
			query += " TIMESTAMP <= '" + finish.toString() +"'";
		}
		
		System.out.println(query);

		ResultSet result = executeQueryAndGetResult(query);
		
		try 
		{
			result.next();
			return result.getInt(1);	// index starting from 1!
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		
		return 0;
	}
	
	public String getPacketsCountsQuery(PacketType packetType, ChartGrain g) 
	{

		String query = "SELECT " + SelectClausure(g) +", '"+packetType.toString()+"', '"+packetType.toString()+"' as Protocol"+
						" FROM " + packetType.toString() +
						" WHERE timestamp<'"+lastAnalyze+"'"+
						" GROUP BY " + GroupByClausure(g) +
						" ORDER BY " + OrderByClausure(g);
				
		//return executeQueryAndGetResult(query);
		return query;
	}
	
	
	public void insert(String table, String select)
	{
		String query = "INSERT INTO "+table
				+"(minute, hour, day, month, year, packetsCount, class, protocol) "
				+select;
		
		executeQuery(query);
		
		
	}
	
	public String GroupByClausure(ChartGrain g)
	{
		String gb="";
		switch(g)
		{
			case Minute : gb=", EXTRACT(MINUTE FROM timestamp)";
			case Hour: gb=", EXTRACT(HOUR FROM timestamp)"+gb;
			case Day: gb=", EXTRACT(DAY FROM timestamp)"+gb;
			case Month: gb=", EXTRACT(MONTH FROM timestamp)"+gb;
			case Year: gb="EXTRACT(YEAR FROM timestamp)"+gb;
		}
		
		return gb;
	}
	
	public String SelectClausure(ChartGrain g)
	{
		String gb="";
		switch(g)
		{
			case Minute : gb="EXTRACT(MINUTE FROM timestamp) as minute,";
			case Hour: gb+="EXTRACT(HOUR FROM timestamp) as hour, ";
			case Day: gb+="EXTRACT(DAY FROM timestamp) as day, ";
			case Month: gb+="EXTRACT(MONTH FROM timestamp) as month, ";
			case Year: gb+="EXTRACT(YEAR FROM timestamp) as year, ";		
		}
		gb+="COUNT(1) as packetsCount ";
		
		return gb;
	}
	
	public String SelectClausure2(ChartGrain g)
	{
		String gb="";
		switch(g)
		{
			case Minute : gb=" minute,";
			case Hour: gb+="hour, ";
			case Day: gb+="day, ";
			case Month: gb+="month, ";
			case Year: gb+="year, ";		
		}
		gb+="SUM(packetsCount) ";
		
		return gb;
	}
	
	public String SelectClausure3(ChartGrain g)
	{
		String gb="";
		switch(g)
		{
			case Minute : gb="EXTRACT(MINUTE FROM timestamp) as minute,";
			case Hour: gb+="EXTRACT(HOUR FROM timestamp) as hour, ";
			case Day: gb+="EXTRACT(DAY FROM timestamp) as day, ";
			case Month: gb+="EXTRACT(MONTH FROM timestamp) as month, ";
			case Year: gb+="EXTRACT(YEAR FROM timestamp) as year ";		
		}
		
		
		return gb;
	}
	
	
	
	public String OrderByClausure(ChartGrain g)
	{
		String gb="";
		switch(g)
		{
			case Minute : gb=", minute "+gb;
			case Hour: gb=", hour"+gb;
			case Day: gb=", day"+gb;
			case Month: gb=", month"+gb;
			case Year: gb="year"+gb;		
		}
		
		return gb;
	}	
	
	public ResultSet getPacketsCounts(String table,String packetType, ChartGrain g)
	{
		String query = "SELECT "+SelectClausure2(g) +
				" FROM "+table+
				" WHERE class='"+packetType+"'"+
				" GROUP BY "+OrderByClausure(g) +", class"+
				" ORDER BY "+OrderByClausure(g);
		return executeQueryAndGetResult(query);
	}
	
	public ResultSet getPacketsCountsFromProtocols(String table,String packetType, 
			String protocols, ChartGrain g)
	{
		String query = "SELECT "+SelectClausure2(g) +
				" FROM "+table+
				" WHERE class='"+packetType+"'"+
				" AND protocol IN " + protocols + " " + 
				" GROUP BY "+OrderByClausure(g) +", class"+
				" ORDER BY "+OrderByClausure(g);
		return executeQueryAndGetResult(query);
	}
	
	public String getPacketsCountsQuery(PacketDirection packetdir, ChartGrain g) 
	{
		String whereC;
		if (packetdir==PacketDirection.Out)
		{
			whereC="TRUE";
		}
		else
		{
			whereC="FALSE";
		}
		
		String query = "SELECT " + SelectClausure(g) +", '"+packetdir.toString()+"', Protocol"+ 
						" FROM  "+
						" ( "+
						" SELECT ID,TIMESTAMP,'ICMPPacket' as Protocol FROM ICMPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" UNION  "+
						" SELECT ID,TIMESTAMP,'TCPPacket' as Protocol FROM TCPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" UNION "+
						" SELECT ID,TIMESTAMP,'UDPPacket' as Protocol FROM UDPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						"UNION"+
						" SELECT ID,TIMESTAMP,'IPPacket' as Protocol FROM IPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" ) "+
						" GROUP BY "+GroupByClausure(g) + ", Protocol"+
						" ORDER BY "+OrderByClausure(g);
		
		return query;
	}
	
	public String getDownUpQuery(PacketDirection packetdir, ChartGrain g) 
	{
		String whereC;
		if (packetdir==PacketDirection.Out)
		{
			whereC="TRUE";
		}
		else
		{
			whereC="FALSE";
		}
		
		String query = "SELECT " + SelectClausure3(g) +", SUM(length), '"+packetdir.toString()+"', Protocol"+
						" FROM  "+
						" ( "+
						" SELECT ID,TIMESTAMP,'ICMPPacket' as Protocol,length  FROM ICMPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" UNION  "+
						" SELECT ID,TIMESTAMP,'TCPPacket' as Protocol,length  FROM TCPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" UNION "+
						" SELECT ID,TIMESTAMP,'UDPPacket' as Protocol, length FROM UDPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" UNION "+
						" SELECT ID,TIMESTAMP,'IPPacket' as Protocol, length FROM IPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" ) "+
						" GROUP BY "+GroupByClausure(g) + ", Protocol"+
						" ORDER BY "+OrderByClausure(g);
		
		return query;
	}
	
	public String getDownUpAvgQuery(PacketDirection packetdir, ChartGrain g) 
	{
		String whereC;
		if (packetdir==PacketDirection.Out)
		{
			whereC="TRUE";
		}
		else
		{
			whereC="FALSE";
		}
		
		String query = "SELECT " + SelectClausure3(g) +", AVG(length), '"+packetdir.toString()+"', Protocol"+
						" FROM  "+
						" ( "+
						" SELECT ID,TIMESTAMP,'ICMPPacket' as Protocol,length  FROM ICMPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" UNION  "+
						" SELECT ID,TIMESTAMP,'TCPPacket' as Protocol,length  FROM TCPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" UNION "+
						" SELECT ID,TIMESTAMP,'UDPPacket' as Protocol, length FROM UDPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" UNION "+
						" SELECT ID,TIMESTAMP,'IPPacket' as Protocol, length FROM IPPACKET "+
						" WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
						" ) "+
						" GROUP BY "+GroupByClausure(g) + ", Protocol"+
						" ORDER BY "+OrderByClausure(g);
		
		return query;
	}
	
	public String getPortQuery(PacketDirection packetdir, ChartGrain g)
	{
		String whereC,dir;
		if (packetdir==PacketDirection.Out)
		{
			whereC="TRUE";
			dir="source";
		}
		else
		{
			whereC="FALSE";
			dir="dest";
		}
		
		
		String query = "SELECT "+SelectClausure(g)+", "+dir+"_port as class, Protocol"+
				" FROM " +
				"(" +
				" SELECT ID, TIMESTAMP, 'TCPPacket' as Protocol, "+dir+"_port FROM TCPPacket WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
				" UNION"+
				" SELECT ID, TIMESTAMP, 'UDPPacket' as Protocol, "+dir+"_port FROM UDPPacket WHERE DIRECTION="+whereC+" and timestamp<'"+lastAnalyze+"'"+
				")" +
				" GROUP BY "+GroupByClausure(g) + ", "+dir+"_port"+ ", Protocol"+
				" ORDER BY "+OrderByClausure(g);
		return query;		
	}
	
	
	public String otherPortQuery(ChartGrain g)
	{
		String query = "SELECT "+SelectClausure(g)+", class, Protocol"+
				" FROM " +
				"("+
				" SELECT ID, TIMESTAMP, 'TCPPacket' as Protocol, source_port as class FROM TCPPacket WHERE DIRECTION=FALSE and timestamp<'"+lastAnalyze+"'"+
				" UNION"+
				" SELECT ID, TIMESTAMP, 'TCPPacket' as Protocol, dest_port as class FROM TCPPacket WHERE DIRECTION=TRUE and timestamp<'"+lastAnalyze+"'"+
				" UNION"+
				" SELECT ID, TIMESTAMP, 'UDPPacket' as Protocol, source_port as class FROM UDPPacket WHERE DIRECTION=FALSE and timestamp<'"+lastAnalyze+"'"+
				" UNION"+
				" SELECT ID, TIMESTAMP, 'UDPPacket' as Protocol, dest_port as class FROM UDPPacket WHERE DIRECTION=TRUE and timestamp<'"+lastAnalyze+"'"+	
				")"+
				" GROUP BY "+GroupByClausure(g) + ", class, Protocol"+
				" ORDER BY "+OrderByClausure(g);
		return query;
	}
	
	public String otherAdressesQuery(ChartGrain g)
	{
		String query = "SELECT "+SelectClausure(g)+", class, Protocol"+
				" FROM " +
				"("+
				" SELECT ID, TIMESTAMP, 'TCPPacket' as Protocol, source_ip as class FROM TCPPacket WHERE DIRECTION=FALSE and timestamp<'"+lastAnalyze+"'"+
				" UNION"+
				" SELECT ID, TIMESTAMP, 'TCPPacket' as Protocol, dest_ip as class FROM TCPPacket WHERE DIRECTION=TRUE and timestamp<'"+lastAnalyze+"'"+
				" UNION"+
				" SELECT ID, TIMESTAMP, 'UDPPacket' as Protocol, source_ip as class FROM UDPPacket WHERE DIRECTION=FALSE and timestamp<'"+lastAnalyze+"'"+
				" UNION"+
				" SELECT ID, TIMESTAMP, 'UDPPacket' as Protocol, dest_ip as class FROM UDPPacket WHERE DIRECTION=TRUE and timestamp<'"+lastAnalyze+"'"+	
				" UNION"+
				" SELECT ID, TIMESTAMP, 'ICMPPacket' as Protocol, source_ip as class FROM ICMPPacket WHERE DIRECTION=FALSE and timestamp<'"+lastAnalyze+"'"+
				" UNION"+
				" SELECT ID, TIMESTAMP, 'ICMPPacket' as Protocol, dest_ip as class FROM ICMPPacket WHERE DIRECTION=TRUE and timestamp<'"+lastAnalyze+"'"+	
				" UNION"+
				" SELECT ID, TIMESTAMP, 'IPPacket' as Protocol, source_ip as class FROM IPPacket WHERE DIRECTION=FALSE and timestamp<'"+lastAnalyze+"'"+
				" UNION"+
				" SELECT ID, TIMESTAMP, 'IPPacket' as Protocol, dest_ip as class FROM IPPacket WHERE DIRECTION=TRUE and timestamp<'"+lastAnalyze+"'"+	
				")"+
				" GROUP BY "+GroupByClausure(g) + ", class, Protocol"+
				" ORDER BY "+OrderByClausure(g);
		return query;
	}	

	public void insertLog(String logType, String logMessage) 
	{
		String query = "INSERT INTO Logs " +
						"(timestamp, " +
						"log_type, " +
						"message) " +
						"VALUES ('" + new Timestamp(new Date().getTime()) + "', '" + 
						logType + "', '" + 
						logMessage + "')";
		
		System.out.println(query);
		executeQuery(query);
	}
	
	private void executeQuery(String query)
	{
		if (conn != null)
		{
			try 
			{
				Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
						ResultSet.CONCUR_UPDATABLE);
				st.execute(query);
			} 
			catch (SQLException e) 
			{
				System.out.println(e.getMessage());
			}
		}
		else
		{
			System.out.println("Couldn't execute query - No connection!");
		}
	}
	
	private ResultSet executeQueryAndGetResult(String query) 
	{
		if (conn != null)
		{
			try 
			{
				Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				return st.executeQuery(query);
			} 
			catch (SQLException e) 
			{
				System.out.println(e.getMessage());
			}
		}
		else
		{
			System.out.println("Couldn't execute query - No connection!");
		}
		
		return null;
	}

	public void deleteTemp() 
	{
		executeQuery(deleteTempQuery(PacketType.TCPPacket));
		executeQuery(deleteTempQuery(PacketType.UDPPacket));
		executeQuery(deleteTempQuery(PacketType.ICMPPacket));
		executeQuery(deleteTempQuery(PacketType.ARPPacket));
		executeQuery(deleteTempQuery(PacketType.IPPacket));
		
		
		
	}
	
	public ResultSet getTopN(String table, int n, String protocols)
	{
		String query="SELECT TOP "+n+"CLASS FROM"+
				" (SELECT SUM(PACKETSCOUNT) AS PACKETSCOUNT , CLASS FROM "+table+
				" WHERE PROTOCOL IN "+protocols+
				" GROUP BY CLASS"+
				" ORDER BY PACKETSCOUNT DESC)";
		return executeQueryAndGetResult(query);
				
	}
	
	
	
	public String deleteTempQuery(PacketType table)
	{
		return "DELETE FROM "+table+" WHERE timestamp<'"+lastAnalyze+"'";
		
	}
}
